import mysql.connector
from mysql.connector import Error


def create_connection():
    """创建到MySQL数据库的连接"""
    connection = mysql.connector.connect(
        host='localhost',
        user='root',
        password='Yzy212121',
        database='final2'
    )
    print("成功连接MySQL数据库")
    return connection


def execute_select(connection, query):
    """执行SQL查询"""
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        results = cursor.fetchall()  # 或者使用fetchone(), fetchmany(size)根据需要
        # 处理查询结果，比如打印
        for row in results:
            print(row)
        return results
    except Error as e:
        print(f"执行查询时发生错误: {e}")
    finally:
        cursor.close()  # 确保游标总是在最后关闭


def execute_transaction(connection, queries, values_list=None):
    cursor = connection.cursor()
    try:
        # 开始事务
        # connection.start_transaction()

        # 遍历SQL语句和对应的参数，执行操作
        for i, query in enumerate(queries):
            cursor.execute(query, values_list[i])
        # 所有操作成功，提交事务
        connection.commit()
        print("事务提交成功")
    except Error as e:
        # 发生错误时回滚事务
        connection.rollback()
        print(f"事务处理中发生错误，已回滚: {e}")
    finally:
        cursor.close()
        # 根据情况决定是否关闭连接，这里假设连接管理在调用者处处理


# 连接到数据库
connection = create_connection()

# 查询示例
# select_query = "SELECT * FROM 学生;"
# execute_select(connection, select_query)

'''
# 插入示例
insert_query = [
    "insert into 学生 values(%s, %s, %s, %s);",
    "delete from 学生 where 学号=%s;"
]
values = [
    (29, 'yuanyuan', 'woman', 20040409),
    (28,) #没有‘,’会报错，因为元组里只有一个元素
] #根据你的表结构调整
execute_transaction(connection, insert_query, values_list=values)  # 注意这里需要调整函数以接受values参数
'''

identity = input("请输入你的身份：1教务老师/2普通教师/3本科生/4研究生")

if identity == '1':
    while True:
        action = input("请输入你要进行的操作：1审批课程/2改变老师信息/3改变学生信息/4改变学院信息（输入exit退出）")
        if action == 'exit':
            break
        if action == '1':  # 使用存储过程来更新审批状态，并把通过的课程添加到通过的课程的表中
            name = input('请输入课程名')
            number = int(input('请输入课号'))
            max_people = int(input('请输入最大选课人数'))
            print('审核前待审批课程如下：')
            select_query = "SELECT * FROM 待审批课程;"
            execute_select(connection, select_query)
            print('审核前已通过课程如下：')
            select_query = "SELECT * FROM 教师提交后通过的课程;"
            execute_select(connection, select_query)

            insert_query = ["call audit(%s,%s,%s,@a);"]
            values = [(name,number,max_people)]
            execute_transaction(connection, insert_query, values_list=values)
            select_query = "SELECT @a;"
            execute_select(connection, select_query)

            print('审核后待审批课程如下：')
            select_query = "SELECT * FROM 待审批课程;"
            execute_select(connection, select_query)
            print('审核后已通过课程如下：')
            select_query = "SELECT * FROM 教师提交后通过的课程;"
            execute_select(connection, select_query)

        if action == '2':
            action2 = input('请输入你要进行的操作：1添加教师')  # 添加教师信息
            if action2 == '1':
                number = int(input("请输入职工号："))
                name = input("请输入姓名：")
                belong_xueyuan = input("请输入所属学院名称：")
                manage_xueyuan = input("请输管理学院名称（若为空则输入null）")
                if manage_xueyuan == 'null':
                    insert_query = ["insert into 教师(职工号,所属学院名称,姓名) values(%s,%s,%s)"]
                    values = [(number,belong_xueyuan,name)]
                    execute_transaction(connection, insert_query, values_list=values)
                    print('教师信息如下：')
                    select_query = "SELECT * FROM 教师;"
                    execute_select(connection, select_query)
                else:
                    insert_query = ["insert into 教师(职工号,所属学院名称,管理学院名称,姓名) values(%s,%s,%s,%s)"]
                    values = [(number,belong_xueyuan,manage_xueyuan,name)]
                    execute_transaction(connection, insert_query, values_list=values)
                    print('教师信息如下：')
                    select_query = "SELECT * FROM 教师;"
                    execute_select(connection, select_query)

        if action == '3':
            action_2 = input("请输入你要进行的操作：1添加/2删除/3更改")
            if action_2 == '1':
                action_3 = input("请输入你要进行的操作：1添加研究生/2添加本科生")
                if action_3 == '2':  # 添加本科生
                    number = int(input("请输入学号:"))
                    name = input('请输入姓名:')
                    gender = input('请输入性别：')
                    day = int(input('请输入生日（YYYYMMDD）：'))
                    name_xueyuan = input('请输入学院名称')
                    code_zhuanye = input('请输入专业代码')
                    insert_query = ["insert into 学生 values(%s, %s, %s, %s);"]
                    values = [(number, name, gender, day)]
                    execute_transaction(connection, insert_query, values_list=values)
                    insert_query = ["insert into 本科生 values(%s);"]
                    values = [(number,)]
                    execute_transaction(connection, insert_query, values_list=values)
                    insert_query = ["insert into 本科生专业及学院 values(%s, %s, %s);"]
                    values = [(name_xueyuan,code_zhuanye,number)]
                    execute_transaction(connection, insert_query, values_list=values)

                    print('学生信息如下：')
                    select_query = "SELECT * FROM 学生;"
                    execute_select(connection, select_query)
                    print('本科生信息如下：')
                    select_query = "SELECT * FROM 本科生;"
                    execute_select(connection, select_query)
            elif action_2 == '2':
                action_3 = input("请输入你要进行的操作：1删除研究生/2删除本科生：")
                if action_3 == '2':  # 删除本科生的信息时，要删除储存他专业和学院的表的相应内容，删除他的选课记录，删除学生总表里他的信息
                    name = input('请输入姓名：')

                    print('学生信息如下：')
                    select_query = "SELECT * FROM 学生;"
                    execute_select(connection, select_query)
                    print('本科生信息如下：')
                    select_query = "SELECT * FROM 本科生;"
                    execute_select(connection, select_query)
                    print('本科生选课信息如下：')
                    select_query = "SELECT * FROM bksxd;"
                    execute_select(connection, select_query)
                    print('本科生专业及学院的表如下：')
                    select_query = "SELECT * FROM 本科生专业及学院;"
                    execute_select(connection, select_query)


                    select_query = "SELECT 本科生.学号 FROM 本科生, 学生 where 本科生.学号=学生.学号 and 学生.姓名='" + name + "';"
                    print('搜索到的学号是：')
                    number = execute_select(connection, select_query)
                    number = int(number[0][0])

                    insert_query = ["delete from bksxd where 学号=%s;",
                                    "delete from 本科生专业及学院 where 学号=%s;",
                                    "delete from 本科生 where 学号=%s;",
                                    "delete from 学生 where 学号=%s;"]
                    values = [(number,),(number,),(number,),(number,)]
                    execute_transaction(connection, insert_query, values_list=values)

                    print('学生信息如下：')
                    select_query = "SELECT * FROM 学生;"
                    execute_select(connection, select_query)
                    print('本科生信息如下：')
                    select_query = "SELECT * FROM 本科生;"
                    execute_select(connection, select_query)
                    print('本科生选课信息如下：')
                    select_query = "SELECT * FROM bksxd;"
                    execute_select(connection, select_query)
                    print('本科生专业及学院的表如下：')
                    select_query = "SELECT * FROM 本科生专业及学院;"
                    execute_select(connection, select_query)
        if action == '4':
            action_2 = input("请输入你要进行的操作：1添加学院")
            if action_2 == '1':  # 添加学院
                name = input("请输入学院名称")
                number = input("请输入院长职工号（若为空则输入null）")
                if number == 'null':
                    insert_query = ["insert into 学院(学院名称) values(%s)"]
                    values = [(name,)]
                    execute_transaction(connection, insert_query, values_list=values)
                    print('学院表如下：')
                    select_query = "SELECT * FROM 学院;"
                    execute_select(connection, select_query)
                else:
                    insert_query = ["insert into 学院 values(%s,%s)"]
                    values = [(name, number)]
                    execute_transaction(connection, insert_query, values_list=values)
                    print('学院表如下：')
                    select_query = "SELECT * FROM 学院;"
                    execute_select(connection, select_query)

elif identity == '2':
    while True:
        action = input("请输入你要进行的操作：1更改个人信息/2申报课程/3查看成绩(输入exit退出)")
        if action == 'exit':
            break
        if action == '2':  # 申报课程
            number = int(input("请输入职工号"))
            classnumber = int(input("请输入课号"))
            name = input("请输入名称")
            point = int(input("请输入学分"))
            insert_query = ["insert into 所有课程（包括待审批） values(%s,%s,%s);",
                            "insert into 待审批课程 values(%s,'未审批');",
                            "insert into 教师提交新课程 values(%s,%s);"]
            values = [(classnumber, name, point),
                      (classnumber,),
                      (classnumber, number)]
            execute_transaction(connection, insert_query, values_list=values)

            print('所有课程表如下：')
            select_query = "SELECT * FROM 所有课程（包括待审批）;"
            execute_select(connection, select_query)
            print('待审批课程表如下：')
            select_query = "SELECT * FROM 待审批课程;"
            execute_select(connection, select_query)

elif identity == '3':
    while True:
        action = input("请输入你要进行的操作：1更改个人信息/2查看成绩单/3选课(输入exit退出)")

        if action == 'exit':
            break
        if action == '1':
            action2 = input("请输入你要进行的操作：1转专业/2修改其他信息")
            if action2 == '1':  # 转专业使用存储过程，如果新的专业不合法则拒绝更新，若合法则更新
                print('转专业前本科生专业及学院的表如下：')
                select_query = "SELECT * FROM 本科生专业及学院;"
                execute_select(connection, select_query)

                number = int(input("请输入学号："))
                new_major = input("请输入新专业名称：")

                insert_query = ["call change_major(%s, %s, @a);"]
                values = [(number, new_major)]
                execute_transaction(connection, insert_query, values_list=values)
                select_query = "SELECT @a;"
                execute_select(connection, select_query)

                print('转专业后本科生专业及学院的表如下：')
                select_query = "SELECT * FROM 本科生专业及学院;"
                execute_select(connection, select_query)

        if action == '3':  # 选课时使用触发器使相应课程的选课人数+1，并检查是否超过最大人数
            number = input('请输入学号：')
            class_name = input('请输入课程名称：')
            print('想选择的课程的课号为', end=':')
            select_query = "SELECT 课号 FROM 所有课程（包括待审批） where 名称='" + str(class_name) + "';"
            class_number = execute_select(connection, select_query)
            class_number = int(class_number[0][0])

            print('选课前本科生选课信息如下：')
            select_query = "SELECT * FROM bksxd;"
            execute_select(connection, select_query)
            print('选课前课程信息如下：')
            select_query = "SELECT * FROM 教师提交后通过的课程;"
            execute_select(connection, select_query)

            insert_query = ["insert into bksxd(学号, 课号) values (%s, %s);"]
            values = [(number, class_number)]
            execute_transaction(connection, insert_query, values_list=values)

            print('选课后本科生选课信息如下：')
            select_query = "SELECT * FROM bksxd;"
            execute_select(connection, select_query)
            print('选课后课程信息如下：')
            select_query = "SELECT * FROM 教师提交后通过的课程;"
            execute_select(connection, select_query)


elif identity == '4':
    while True:
        action = input("请输入你要进行的操作：1更改个人信息/2查看导师信息(输入exit退出)")
        if action == 'exit':
            break
        if action == '2':  # 用视图把研究生&教师连起来，研究生查看导师姓名时在视图里找自己的学号即可
            number = input('请输入学号：')
            print('视图中的信息如下：')
            select_query = "SELECT * FROM stu_teacher;"
            execute_select(connection, select_query)

            print('你的导师信息如下：')
            select_query = "SELECT 职工号,所属学院名称,姓名 FROM stu_teacher where 学号=" + str(number) + ";"
            execute_select(connection, select_query)

else:
    print("输入不合法")

# 关闭连接，通常在程序结束时执行
if connection.is_connected():
    connection.close()
    print("MySQL连接已关闭")
